PostgreSQL 数据库性能 Cluster表提升索引访问性能

1 本章背景知识

本章主要介绍堆表（Heap Table）和（Cluster Table）聚簇表的概念和性能调优。

2 Heap Table（堆表）

2.1 Heap Table 特点

1、表中的每一行都存储在数据文件中，数据文件又称之为堆文件。
2、在表上创建索引时，索引将字段中的值进行排序，然后存储到索引中。
3、索引能够快速查找所需的值，并通过 ctid 可以快速找到匹配的行。这种方式叫做回表。
4、堆表以无序的方式插入、更新和删除行，这将导致在数据堆文件中也无序。

2.2 Heap Table 缺点

1、通常情况下，索引就能满足对性能的需求。
2、特殊情况下，索引的有序与堆表的无序会导致性能问题。

Note

此处的性能问题是指，索引将会回表多个数据块，降低回表效率。

3、以其中一个索引的顺序，对Cluster Table堆表进行排序，从而提高性能。

Warning

注意：非btree索引不能被聚簇，因为它们缺乏线性排序。

3 Cluster Table（聚簇表）

3.1 Cluster Table 特点

1、Cluster 表必须有索引，根据索引才能聚簇指定的表。
2、Cluster 表类似于 Oracle 的索引组织表，与指定的索引顺序一致。

3.2 Cluster Table 如何提高性能？

1、当检索一行时，聚簇表和堆表的性能差距并不明显。
2、当检索连续的1000行时：
（1）如果是堆表，并且1000行分散在1000个8kB的页上，则需要许多I/O访问。
（2）如果是聚簇表，这些行都在相邻的页面上，这将减少I/O访问。

3.3 Cluster Table 的缺点

1、当一个表被聚簇时，会在其上要求一个 ACCESS EXCLUSIVE 锁。这会阻止任何其他数据库操作（包括读和写）。
2、与索引组织表不同，堆表不会保持有序状态。
（1）其后的插入和更新操作会将行以非顺序的放置在堆中，导致堆表变得不那么有序。
（2）需要定期 Cluster 操作来恢复的排序。

4 Create Cluster TABLE

1、 CLUSTER 不能在一个事务块内执行。

BEGIN;
CLUSTER table_name;
COMMIT;

2、对表重新聚簇。

CLUSTER table_name

3、清除所有聚簇表的设置。

ALTER TABLE table_name SET WITHOUT CLUSTER;

4、不带任何参数的 CLUSTER 会重新聚簇调当前数据库中已经被聚簇过的表。

Warning

如果是超级用户调用，则是所有已被聚簇过的表。

CLUSTER

5 Cluster TABLE 适用场景

以下场景，对堆表的页进行排序可以大大减少I/O访问的次数。
1、索引列单个值：具有大量数据，例如，检索colname=5返回大量数据。
2、访问索引列的范围值：例如，colname>=10 and colname<20。
3、读取经常访问的值：例如未结算订单。

6 字段的关联性概念

sys_stats.correlation 显示物理行顺序和字段值逻辑顺序之间的统计关联。

1、关联性的范围从-1到+1。
2、当逻辑顺序和物理顺序相同时，sys_stats.correlation 为1。
3、当逻辑顺序和物理顺序相反时，sys_stats.correlation 为-1。
4、当逻辑顺序和物理顺序随机时，其范围从-1到+1。
5、当值接近 -1 或 +1 时，在字段上的一个索引扫描的代价更低，因为这种情况减少了对磁盘的随机访问。

7 关联性高的场景

7.1 环境准备

DROP TABLE  public.cluster_list;
CREATE TABLE public.cluster_list (col1 INTEGER, col2 TEXT);
CREATE INDEX i_cluster_list ON cluster_list (col1 );
INSERT INTO public.cluster_list SELECT *, repeat('col1', 250) FROM generate_series(1, 100000);

7.2 收集统计信息

ANALYZE cluster_list;

7.3 查看关联性

SELECT correlation FROM pg_stats WHERE schemaname = 'public' AND tablename = 'cluster_list' AND attname = 'col1';
//屏幕输出：

  correlation
---------------
1

7.4 查看执行计划

7.4.1 索引扫描

当col1 小于 89000 的执行计划。

EXPLAIN (analyze,buffers) SELECT * FROM cluster_list WHERE col1 < 89000;
//屏幕输出：
QUERY PLAN
---------------------------------------------------------------
 Index Scan using i_cluster_list on cluster_list  (cost=0.29..15246.95 rows=88895 width=1008) (actual time=0.056..30.909 rows=88999 loops=1)
   Index Cond: (col1 < 89000)
   Buffers: shared hit=12961
 Planning Time: 0.082 ms
 Execution Time: 34.712 ms
(5 行记录)

7.4.2 全表扫描

当 col1 小于 90000 的执行计划。

EXPLAIN SELECT * FROM cluster_list WHERE col1 < 90000;
QUERY PLAN
-----------------------------------------------------------------------
 Seq Scan on cluster_list  (cost=0.00..15536.00 rows=89967 width=1008)
   Filter: (col1 < 90000)
(2 行记录)

1、优化器在 89k 和 90k 行访问之间从索引扫描切换到顺序扫描。
2、当索引列的顺序与表数据的顺序相关性较高时，索引的代价就比较低。

Note

实验证明查询全表90%以上的数据量时才会转换到全表扫描。

8 关联性低的场景

8.1 环境准备

DROP TABLE public.cluster_list;
CREATE TABLE public.cluster_list (col1 INTEGER, col2 TEXT);
CREATE INDEX i_cluster_list ON cluster_list (col1);
INSERT INTO public.cluster_list SELECT int, md5(random()::text) FROM generate_series(1, 100000;

8.2 收集统计信息

ANALYZE cluster_list;

8.3 查看关联性

sys_stats.correlation 显示物理行顺序和字段值逻辑顺序之间的统计关联。

SELECT correlation FROM sys_stats WHERE schemaname = 'public' AND tablename = 'cluster_list' AND attname = 'col1';
//屏幕输出：
 correlation
--------------
 -0.008655776
(1 行记录)

Note

目前关联性低，表示数据的逻辑顺序和物理顺序是随机的。

8.4 查看执行计划

8.4.1 位图扫描

col1 小于 36000 时的执行计划。

EXPLAIN (analyze,buffers) SELECT * FROM cluster_list WHERE col1 < 36000;
//屏幕输出：
QUERY PLAN
-----------------------------------------------------
 Bitmap Heap Scan on cluster_list  (cost=764.15..2056.54 rows=36111 width=37) (actual time=2.531..7.327 rows=36160 loops=1)
   Recheck Cond: (col1 < 36000)
   Heap Blocks: exact=841
   Buffers: shared hit=968
   ->  Bitmap Index Scan on i_cluster_list  (cost=0.00..755.12 rows=36111 width=0) (actual time=2.437..2.437 rows=36160 loops=1)
         Index Cond: (col1 < 36000)
         Buffers: shared hit=127
 Planning Time: 0.079 ms
 Execution Time: 8.737 ms
(9 行记录)

8.4.2 全表扫描

col1 小于37000 时的执行计划。


EXPLAIN (analyze,buffers) SELECT * FROM cluster_list WHERE col1 < 37000;
//屏幕输出：

QUERY PLAN
-------------------------------------
 Seq Scan on cluster_list  (cost=0.00..2091.00 rows=37033 width=37) (actual time=0.019..9.935 rows=37184 loops=1)
   Filter: (col1 < 37000)
   Rows Removed by Filter: 62816
   Buffers: shared hit=841
 Planning Time: 0.089 ms
 Execution Time: 11.356 ms
(6 行记录)

1、索引列的顺序与表数据的顺序是无相关性的，索引的代价就更高。
2、可以看到优化器在 36k 和 37k 行访问之间从索引扫描切换到顺序扫描。

Note

换句话说，更少数据量时就转换到全表扫描。

9 使用Cluster 表提高关联性

可以强制根据索引排序，进行表的逻辑排序，提高索引的性能。

9.1 使用Cluster

CLUSTER cluster_list USING i_cluster_list;
ANALYZE cluster_list;

9.2 查看关联性

SELECT correlation FROM sys_stats WHERE schemaname = 'public' AND tablename = 'cluster_list' AND attname = 'col1';
//屏幕输出：
 correlation
-------------
           1
(1 行记录)

9.3 查看执行计划

9.3.1 索引扫描

EXPLAIN SELECT * FROM cluster_list WHERE col1 < 55000;
//屏幕输出：
QUERY PLAN
--------------------
 Index Scan using i_cluster_list on cluster_list  (cost=0.42..2041.66 rows=54871 width=37)
   Index Cond: (col1 < 55000)
(2 行记录)

9.3.2 全表扫描

EXPLAIN SELECT * FROM cluster_list WHERE col1 < 56000;
//屏幕输出：
QUERY PLAN
------------------------------------------
 Seq Scan on cluster_list  (cost=0.00..2091.00 rows=55804 width=37)
   Filter: (col1 < 56000)
(2 行记录)

1、索引列的顺序与表数据的顺序是相关性较高的，索引的代价较低。
2、可以看到优化器在 55k 和 56k 行访问之间从索引扫描切换到顺序扫描。

10 小结

1、连续时间类型的数据，不需要 cluster table，因为近期的数据是最常访问的。
2、如果表几乎没有更新和删除，新行会追加到文件的末尾，自然有良好的相关性。
3、表分区可以被认为是一种粗略的聚簇表，因为可通过使用基于时间的分区来改善数据关联性，提高数据库性能。